﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;
using System.Data.SqlClient;
using System.Data;

namespace FlightSystemDBLayer
{
    public class DBBooking: IBooking
    {
        SQLAccess c;
        public DBBooking()
        {
            c = new SQLAccess();
        
        }

        public void writeData(Reservation r) {
           
               
                SqlDataAdapter da = new SqlDataAdapter();
                SqlDataAdapter da2 = new SqlDataAdapter();

                da.InsertCommand = new SqlCommand("INSERT INTO Bookings VALUES(@FirstName,@LastName,@Address, @Country, @Phone, @Email, @PassportID, @FlightID, @IDConnection, @noOfSeats)", c.dbconn);
                da2.InsertCommand = new SqlCommand("UPDATE Flight SET NumberOfSeats = " + r.Flight.NoOfSeats + " WHERE IDFlight = " + r.Flight.Id + "",  c.dbconn);



                da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = r.Fname;
                da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = r.Lname;
                da.InsertCommand.Parameters.Add("@Address", SqlDbType.VarChar).Value = r.Address;
                da.InsertCommand.Parameters.Add("@Country", SqlDbType.VarChar).Value = r.Country;
                da.InsertCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = r.Phone;
                da.InsertCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = r.Email;
                da.InsertCommand.Parameters.Add("@PassportID", SqlDbType.VarChar).Value = r.Passport;
                da.InsertCommand.Parameters.Add("@FlightID", SqlDbType.Int).Value = r.Flight.Id;
                da.InsertCommand.Parameters.Add("@IDConnection", SqlDbType.Int).Value = r.Conn.Id;
                da.InsertCommand.Parameters.Add("@noOfSeats", SqlDbType.Int).Value = r.NoOfSeats;

                c.dbconn.Open();
                da.InsertCommand.ExecuteNonQuery();
                da2.InsertCommand.ExecuteNonQuery();
                c.dbconn.Close();
           
        }
        public Connection updateConnection(Connection con) { 
        
            foreach (Flight item in con.Flights)
	        {
		        
	        
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"SELECT NumberOfSeats FROM Flight WHERE IDFlight = " + item.Id, c.dbconn);
            da.Fill(ds);

            
            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                item.NoOfSeats = Convert.ToInt32(dr[0]); 
                

            }
            }
            return con;
        }
        public void writeConnection(Connection Conn, int seats){

          
             
            SqlDataAdapter con = new SqlDataAdapter();

            con.InsertCommand = new SqlCommand("INSERT INTO Connection VALUES(@IDConnection, @DepartureCity, @ArrivalCity, @DepartureTime, @ArrivalTime, @TotalPrice, @NumberOfFlightChanges, @SeatsTaken )" , c.dbconn);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"SELECT MAX(IDConnection) FROM Connection", c.dbconn);
            da.Fill(ds);

            int id = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                id = Convert.ToInt32(dr[0]); // when there is nothing in table gives exception, fix or not to fix, that is the question.. 
                

            }
            id++;
            Conn.Id = id;
            int totalPriceForAllSeats = Conn.TotalPrice * seats;
            con.InsertCommand.Parameters.Add("IDConnection", SqlDbType.Int).Value = id;
            con.InsertCommand.Parameters.Add("DepartureCity", SqlDbType.Int).Value = Conn.DepartureCity.Id;
            con.InsertCommand.Parameters.Add("ArrivalCity", SqlDbType.Int).Value = Conn.DestinationCity.Id;
            con.InsertCommand.Parameters.Add("DepartureTime", SqlDbType.DateTime).Value = Conn.DepartureTime;
            con.InsertCommand.Parameters.Add("ArrivalTime", SqlDbType.DateTime).Value = Conn.ArrivalTime;
            con.InsertCommand.Parameters.Add("TotalPrice", SqlDbType.Int).Value = totalPriceForAllSeats;
            con.InsertCommand.Parameters.Add("NumberOfFlightChanges", SqlDbType.Int).Value = Conn.NumberOfFlightChange;
            con.InsertCommand.Parameters.Add("SeatsTaken", SqlDbType.Int).Value = seats;


            c.dbconn.Open();
            con.InsertCommand.ExecuteNonQuery();
            c.dbconn.Close();
        }
    }
}
